USE scm_main;
if exists (select 1
          from sysobjects
          where  id = object_id('bd_door_access')
          and type in ('P','PC'))
DROP PROCEDURE bd_door_access
GO
CREATE PROCEDURE bd_door_access
	@in_door_rule_no varchar(max),
	@out_door_rule_no varchar(max),
	@user_serial_str varchar(max),
	@door_bh_str varchar(max),
	@ip varchar(max),
	@operator varchar(max)
as
BEGIN

-- 关闭打印受影响行数
SET NOCOUNT ON;


-- 定义返回受影响行数结果
	declare @result int = 0;
	

	--  异常扑捉机制
		BEGIN TRY  
		
		--  1.将数据拆分放进各自的临时表
			select * into #user_temp from string_split(@user_serial_str,',');
			select * into #door_temp from string_split(@door_bh_str,',');

			-- 创建授权名单表
			CREATE TABLE #user_door_temp(
				  [user_serial] nvarchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
				  [door_bh] nvarchar(50) COLLATE Chinese_PRC_CI_AS  NULL
			);

		-- 开启事务
			BEGIN TRAN

		--  2.声明变量
			declare @sum int = 0;		--遍历用户总个数
			declare @index int = 0;	--当前遍历用户索引
			declare @user_serial varchar(50)	--当前遍历用户编号


		--	3.将数据插入到临时表中
			select @sum = count(0) from #user_temp;
			WHILE (@index < @sum)
			BEGIN
		
				select @user_serial = items from #user_temp order by items offset @index rows fetch next 1 rows only

				insert into #user_door_temp(user_serial,door_bh)
				select @user_serial,* from #door_temp
		
				set @index = @index + 1;
			END


		--  4.清除已经在的权限名单
			delete r 
			from Mj_user_rule_real r
			inner join dt_user u on r.user_serial = u.user_serial
			inner join #user_door_temp ud on r.user_serial = ud.user_serial and r.Gate_bh = ud.door_bh
			--where u.user_type < 51

	
		--  5.授权进门权限
			insert into Mj_user_rule_real(user_serial,Gate_bh,FX,rule_no,sj,gly_no)
			select user_serial,door_bh,0,@in_door_rule_no,GETDATE(),'super' from #user_door_temp


		--  6.授权出门权限
			insert into Mj_user_rule_real(user_serial,Gate_bh,FX,rule_no,sj,gly_no)
			select user_serial,door_bh,1,@out_door_rule_no,GETDATE(),'super' from #user_door_temp

	
		--  7.通知《授权进门权限  101,1,0,卡号,进门规则,设备端口号,进出方向,不知道干啥的
			insert into jreal_update_1(jdev_bh,jdata_str)
			select a.dev_serial as jdev_bh,concat('101,1,0,',a.user_card,',',@in_door_rule_no,',',a.dev_order,',','1',',',isnull( isnull(left(CONVERT(varchar(100), a.pact_begin, 20),10),'2000-01-01')+' 00:00:00,' + left(CONVERT(varchar(100), a.pact_end, 20),10) +' 23:59:59','' ) ) as jdata_str from (
			--select a.dev_serial as jdev_bh,'101,1,0,'+a.user_card+','+@in_door_rule_no+','+a.dev_order+','+'0'+','+isnull( isnull(left(CONVERT(varchar(100), a.pact_begin, 20),10),'2000-01-01')+' 00:00:00,' + left(CONVERT(varchar(100), a.pact_end, 20),10)+' 23:59:59' ,'') as jdata_str from (
				select temp.door_bh,dev_serial,u.user_card,dev_order,u.pact_begin,u.pact_end
				from ST_DOORFX_REAL sdr
				inner join #user_door_temp temp on sdr.gate_bh = temp.door_bh
				inner join dt_user u on u.user_serial = temp.user_serial
				where sdr.fx = 0 -- 进
			) as a


		--  8.通知《授权出门权限  101,1,0,卡号,进门规则,设备端口号,进出方向,不知道干啥的
			insert into jreal_update_1(jdev_bh,jdata_str)
			select a.dev_serial as jdev_bh,CONCAT('101,1,0,',a.user_card,',',@out_door_rule_no,',',a.dev_order,',','0',',',isnull( isnull(left(CONVERT(varchar(100), a.pact_begin, 20),10),'2000-01-01')+' 00:00:00,' + left(CONVERT(varchar(100), a.pact_end, 20),10) +' 23:59:59','' ) ) as jdata_str from (
			--select a.dev_serial as jdev_bh,'101,1,0,'+a.user_card+','+@in_door_rule_no+','+a.dev_order+','+'1'+','+isnull( isnull(left(CONVERT(varchar(100), a.pact_begin, 20),10),'2000-01-01')+' 00:00:00,' + left(CONVERT(varchar(100), a.pact_end, 20),10)+' 23:59:59' ,'') as jdata_str from (
				select temp.door_bh,dev_serial,u.user_card,dev_order,u.pact_begin,u.pact_end
				from ST_DOORFX_REAL sdr
				inner join #user_door_temp temp on sdr.gate_bh = temp.door_bh
				inner join dt_user u on u.user_serial = temp.user_serial
				where sdr.fx = 1 -- 出
			) as a
			
		--  9.权限日志
			insert into wt_mj_log_real(lx,log_type,log_state,log_bz,log_sj,log_ip,gly_no,regserial)
			select 0,3,0,CONCAT('为【',a.door_name,':',a.bh,'】授权,人员:【',user_lname,':',a.user_serial,'】,卡号:【',a.user_card,'】'),GETDATE(),@ip,@operator,''
			from 
			(
				select d.bh,d.door_name,u.user_serial,u.user_lname,u.user_card
				from ST_DOORFX_REAL sdr
				inner join st_door_real d on d.bh = sdr.gate_bh
				inner join #user_door_temp temp on sdr.gate_bh = temp.door_bh 
				inner join dt_user u on u.user_serial = temp.user_serial
				where sdr.fx = 0
			) as a
			
			set @result = @@ROWCOUNT;
		
		--  提交事务	
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			ROLLBACK TRAN;
		END CATCH



--  清除临时表
	select @result as result
	drop table #user_temp;
	drop table #door_temp;
	drop table #user_door_temp;
	
-- 打开打印受影响行数
SET NOCOUNT OFF;
END;
